package hypeerweb;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;

/**
 * The HyPeerWeb Database Domain: DATABASE_DIRECTORY : String DEFAULT_DATABASE_NAME : String HyPeerWebDatabase : String dbName : String
 * 
 * @author Brian (Javadoc by Jared)
 */
public class HyPeerWebDatabase {
	/**
	 * The default database name
	 */
	public static String DEFAULT_DATABASE_NAME = "HyPeerWeb.db";
	/**
	 * The singleton
	 */
	private static HyPeerWebDatabase singleton;
	/**
	 * The database name
	 */
	private String dbName;

	/**
	 * This makes a new database with the given name.
	 * 
	 * @param String
	 *            dbName
	 */
	private HyPeerWebDatabase(String dbName) {
		assert dbName != null;
		this.dbName = dbName;
		Connection conn = getConnection();
		
		try {
			DatabaseMetaData dbm = conn.getMetaData();
			ResultSet tables = dbm.getTables(null, null, null, null);
			
			if(!tables.next()) {
				HyPeerWebDatabaseCreator.createDatabase(dbName);
			}
		} catch (Exception ex) {
			System.out.println("Error creating database: " + ex.getMessage());
		} finally {
			closeConnection(conn);
		}
	}

	/**
	 * This initializes the database in the singleton.
	 */
	public static void initHyPeerWebDatabase() {
		initHyPeerWebDatabase(null);
	}

	/**
	 * This initializes the database in the singleton.
	 * 
	 * @param String
	 *            dbName
	 */
	public static void initHyPeerWebDatabase(String dbName) {
		if (dbName == null) {
			singleton = new HyPeerWebDatabase(DEFAULT_DATABASE_NAME);
		} else {
			singleton = new HyPeerWebDatabase(dbName);
		}
	}

	/**
	 * Gets the single HyPeerWebDatabase.
	 * 
	 * @return HyPeerWebDatabase
	 */
	public static HyPeerWebDatabase getSingleton() {
		assert singleton != null;
		return singleton;
	}

	public void clear() {
		try {
			HyPeerWebDatabaseCreator.createDatabase(this.dbName);
		} catch (Exception ex) {
			System.out.println("Error clearing database: " + ex.getMessage());
		}
	}

	/**
	 * Gets the connection to the database
	 * 
	 * @return Connection
	 */
	public Connection getConnection() {
		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + dbName);
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		return conn;
	}

	/**
	 * Attempts to close the given connection
	 * 
	 * @param Connection
	 *            conn
	 */
	public void closeConnection(Connection conn) {
		try {
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Retrieves all nodes in the database
	 * 
	 * @return Iterable<SimplifiedNodeDomain>
	 */
	public Iterable<SimplifiedNodeDomain> getNodes() {
		HashSet<SimplifiedNodeDomain> simpNodes = new HashSet<SimplifiedNodeDomain>();
		HashSet<Integer> webIds = new HashSet<Integer>();
		Connection conn = getConnection();

		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("SELECT webid FROM node;");

			while (rs.next()) {
				webIds.add(rs.getInt("webid"));
			}
		} catch (Exception ex) {
			// System.out.println("Error accessing database: " +
			// ex.getMessage());
		} finally {
			closeConnection(conn);
		}

		for (int webId : webIds) {
			simpNodes.add(getNode(webId));
		}

		return simpNodes;
	}

	/**
	 * Creates a SimplifiedNodeDomain representing the node with indicated webId
	 * 
	 * @param int webId
	 * @return SimplifiedNodeDomain
	 */
	public SimplifiedNodeDomain getNode(int webId) {
		assert webId >= 0;
		int height, fold, surrogateFold, inverseSurrogateFold, state;
		HashSet<Integer> neighbors, upPointers, downPointers;

		state = -1;
		height = 0;
		fold = -1;
		surrogateFold = -1;
		inverseSurrogateFold = -1;

		neighbors = new HashSet<Integer>();
		upPointers = new HashSet<Integer>();
		downPointers = new HashSet<Integer>();

		Connection conn = getConnection();

		try {
			Statement stat = conn.createStatement();

			// query db for height, fold, and surrogateFold
			ResultSet resultNode = stat.executeQuery("SELECT * FROM node WHERE webid = " + webId + ";");
			if (resultNode.next()) {
				state = resultNode.getInt("state");
				height = resultNode.getInt("height");
				fold = resultNode.getInt("fold");
				surrogateFold = resultNode.getInt("surrogateFold");

				// query db for inverseSurrogateFold
				ResultSet resultInverseSurrogateFoldNode = stat.executeQuery("SELECT * FROM node WHERE surrogateFold = " + webId + ";");
				if (resultInverseSurrogateFoldNode.next()) {
					inverseSurrogateFold = resultInverseSurrogateFoldNode.getInt("webId");
				}

				neighbors = getNeighbors(webId, stat);
				downPointers = getSurrogateNeighbors(webId, stat);
				upPointers = getInverseSurrogateNeighbors(webId, stat);
			} else {
				System.out.println("Node " + webId + " not found.");
			}
		} catch (Exception ex) {
			System.out.println("Error getting node from database: " + ex.getMessage());
		} finally {
			closeConnection(conn);
		}

		return new SimplifiedNodeDomain(webId, height, neighbors, upPointers, downPointers, fold, surrogateFold, inverseSurrogateFold, state);
	}

	/**
	 * Query Database for neighbors in both directions.
	 * 
	 * @param int webId
	 * @param Statement
	 *            stat
	 * @return HashSet<Ingeger>
	 * @throws SQLException
	 */
	private HashSet<Integer> getNeighbors(int webId, Statement stat) throws SQLException {
		assert webId >= 0 && stat != null;

		HashSet<Integer> neighbors = new HashSet<Integer>();
		ResultSet resultNeighbors = stat.executeQuery("SELECT * FROM neighbor WHERE node1 = " + webId);

		while (resultNeighbors.next()) {
			neighbors.add(resultNeighbors.getInt("node2"));
		}

		resultNeighbors = stat.executeQuery("SELECT * FROM neighbor WHERE node2 = " + webId);

		while (resultNeighbors.next()) {
			neighbors.add(resultNeighbors.getInt("node1"));
		}

		return neighbors;
	}

	/**
	 * Query the database for surrogate neighbors
	 * 
	 * @param int webId
	 * @param Statement
	 *            stat
	 * @return HashSet<Integer>
	 * @throws SQLException
	 */
	private HashSet<Integer> getSurrogateNeighbors(int webId, Statement stat) throws SQLException {
		assert webId >= 0 && stat != null;

		HashSet<Integer> surrogateNeighbors = new HashSet<Integer>();
		ResultSet resultSurrogateNeighbors = stat.executeQuery("SELECT * FROM surrogateNeighbor WHERE node1 = " + webId);

		while (resultSurrogateNeighbors.next()) {
			surrogateNeighbors.add(resultSurrogateNeighbors.getInt("node2"));
		}

		return surrogateNeighbors;
	}

	/**
	 * Query the Database for inverse surrogate neighbors
	 * 
	 * @param int webId
	 * @param Statement
	 *            stat
	 * @return HashSet<Integer>
	 * @throws SQLException
	 */
	private HashSet<Integer> getInverseSurrogateNeighbors(int webId, Statement stat) throws SQLException {
		assert webId >= 0 && stat != null;

		HashSet<Integer> inverseSurrogateNeighbors = new HashSet<Integer>();
		ResultSet resultInverseSurrogateNeighbors = stat.executeQuery("SELECT * FROM surrogateNeighbor WHERE node2 = " + webId);

		while (resultInverseSurrogateNeighbors.next()) {
			inverseSurrogateNeighbors.add(resultInverseSurrogateNeighbors.getInt("node1"));
		}

		return inverseSurrogateNeighbors;
	}

	/**
	 * Resets the database with the given nodes
	 * 
	 * @param Iterable
	 *            <Node> nodes
	 * @throws Exception
	 */
	void resetDatabase(Iterable<SimplifiedNodeDomain> nodes) throws Exception {
		Connection conn = getConnection();

		try {
			Statement stat = conn.createStatement();
			stat.execute("DELETE FROM Node;");
			stat.execute("DELETE FROM Neighbor;");
			stat.execute("DELETE FROM SurrogateNeighbor;");

			for (SimplifiedNodeDomain n : nodes) {
				stat.execute("INSERT INTO Node VALUES (" + n.getWebId() + "," + n.getState() + "," + n.getHeight() + "," + n.getFold() + "," + n.getSurrogateFold() + ");");

				for (int neighbor : n.getNeighbors()) {
					stat.execute("INSERT INTO Neighbor VALUES (" + n.getWebId() + "," + neighbor + ");");
				}

				for (int surrogateNeighbor : n.getDownPointers()) {
					stat.execute("INSERT INTO SurrogateNeighbor VALUES (" + n.getWebId() + "," + surrogateNeighbor + ");");
				}
			}
		} catch (Exception ex) {
			System.out.println("Error adding info to database: " + ex.getMessage());
		} finally {
			closeConnection(conn);
		}
	}
}
